﻿'Description:排产--工位维护类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Longb
'create Date:2022-08-11
'Rewriter:
'Rewrite Date:

Imports DatabaseAccess.DBUtility
Imports BusinessRule.DTO
Imports System.Text
Imports External.APICalling
Public Class WorkStationBusiness
    ''' <summary>
    ''' 查询分厂组信息
    ''' </summary>
    ''' <param name="factoryid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetShiftByFacyoryId(ByVal factoryid As String) As DataTable
        Dim StrSql = "SELECT SHIFTID,SHIFTNAME FROM SHIFT WHERE FACTORYID='" + factoryid + "'"
        Return DatabaseHelper.Query_ReturnDatTable(StrSql)
    End Function
    ''' <summary>
    ''' 工位维护--查询
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkStation(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim StrSql As New StringBuilder
        StrSql.AppendLine(<string>
                    SELECT f.FACTORYNAME,t.SHIFTNAME,w.WORKSTATIONID,w.FACTORYID,w.SHIFTID,w.WORKSTATIONNAME,w.QUOTAMINS,e.FULLNAME,to_char(w.CREATEDATE,'yyyy-MM-dd hh24:mm:ss') CREATEDATE,to_char(w.LASTCHANGEDATE,'yyyy-MM-dd hh24:mm:ss') LASTCHANGEDATE--,decode(w.IsBottLeneck,'1','是','否')IsBottLeneck
                    FROM WorkStation w
                    LEFT JOIN FACTORY f ON f.FACTORYID =w.FACTORYID 
                    LEFT JOIN SHIFT t ON t.SHIFTID =w.SHIFTID 
                    LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =w.CREATEEMPLOYEEID
                    WHERE 1=1  
                          </string>)
        If para.ContainsKey("factoryId") Then
            If Not String.IsNullOrWhiteSpace(para("factoryId")) Then
                StrSql.AppendFormat(" and w.FACTORYID='{0}'", para("factoryId"))
            End If
        End If
        If para.ContainsKey("shiftId") Then
            If Not String.IsNullOrWhiteSpace(para("shiftId")) Then
                StrSql.AppendFormat(" and w.SHIFTID='{0}'", para("shiftId"))
            End If
        End If
        Return DatabaseHelper.Query_ReturnDatTable(StrSql.ToString)
    End Function
    ''' <summary>
    ''' 工位维护--保存
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertWorkStation(ByVal para As Dictionary(Of String, String)) As String
        Dim StrSql As New StringBuilder
        StrSql.AppendLine(<string>INSERT INTO WorkStation(WorkStationID,WorkStationName,ShiftID,FactoryID,QuotaMins,CreateDate,CreateEmployeeID,LastChangeDate)</string>)
        StrSql.AppendFormat("VALUES('{0}','{1}','{2}','{3}','{4}',sysdate,'{5}',sysdate)", para("WorkStationID"), para("WorkStationName"), para("ShiftID"), para("FactoryID"), para("QuotaMins"), para("CreateEmployeeID"))
        Return StrSql.ToString
    End Function
    ''' <summary>
    ''' 工位维护--更新
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateWorkStation(ByVal para As Dictionary(Of String, String)) As String
        Dim StrSql As New StringBuilder
        StrSql.AppendLine(<string> UPDATE WorkStation SET </string>)
        StrSql.AppendFormat("ShiftID='{0}',FactoryID='{1}',QuotaMins='{2}',LastChangeDate=sysdate ", para("ShiftID"), para("FactoryID"), para("QuotaMins"))
        If para.ContainsKey("WorkStationName") Then
            If Not String.IsNullOrWhiteSpace(para("WorkStationName")) Then
                StrSql.AppendFormat(" ,WorkStationName='{0}'", para("WorkStationName"))
            End If
        End If
        StrSql.AppendFormat(" WHERE WorkStationID='{0}'", para("WorkStationID"))
        Return StrSql.ToString
    End Function
    ''' <summary>
    ''' 工位/人员关系--绑定
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function BindWorkStationEmployee(ByVal para As Dictionary(Of String, String), ByVal dt As DataTable, ByVal mainSql As String) As Boolean
        Dim sqlList As New ArrayList
        sqlList.Add(mainSql)
        Dim strSql As String = ""
        strSql = String.Format("delete from WorkStationEmployee t where t.WorkStationID='{0}'", para("WorkStationID")) '清除功能
        sqlList.Add(strSql)
        For Each dr As DataRow In dt.Rows
            Dim employeeid As String = dr("employeeid").ToString
            strSql = String.Format("insert into WorkStationEmployee(WorkStationID,employeeid,WorkStationEmployeeID) values('{0}','{1}',SYS_GUID()) ", para("WorkStationID"), employeeid)
            sqlList.Add(strSql)
        Next
        Return DatabaseHelper.ExecuteSqlListTran(sqlList)
    End Function
    ''' <summary>
    ''' 工位/人员关系--查询
    ''' </summary>
    ''' <param name="WorkStationID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkStationEmployee(ByVal WorkStationID As String) As DataTable
        Dim StrSql = "SELECT e.EMPLOYEEID,e.FULLNAME,e.EMPLOYEENAME FROM WORKSTATIONEMPLOYEE w "
        StrSql += " LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =w.EMPLOYEEID"
        StrSql += " WHERE w.WORKSTATIONID ='" + WorkStationID + "'"
        Return DatabaseHelper.Query_ReturnDatTable(StrSql)
    End Function
    ''' <summary>
    ''' 判断同一分厂、班是否已存在--工位名
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CheckExistsWorkStationName(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim StrSql As New StringBuilder
        StrSql.AppendFormat("SELECT * FROM WORKSTATION w WHERE w.FACTORYID ='{0}' AND w.ShiftID ='{1}' AND w.WORKSTATIONNAME='{2}'", para("FactoryID"), para("ShiftID"), para("WorkStationName"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(StrSql.ToString)
        Return dt
    End Function
    ''' <summary>
    ''' 工位人员信息导入
    ''' </summary>
    ''' <param name="maindt"></param>
    ''' <param name="dedt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function ImportWorkStationEmployee(ByVal maindt As DataTable, ByVal dedt As DataTable) As Boolean
        Dim sqlList As New ArrayList
        For Each dr As DataRow In maindt.Rows
            sqlList.Add(dr("mainSql").ToString)
            sqlList.Add(String.Format("delete from WorkStationEmployee t where t.WorkStationID='{0}'", dr("workStationid").ToString))
        Next
        For Each dr As DataRow In dedt.Rows
            Dim employeeid As String = dr("employeeid").ToString
            sqlList.Add(String.Format("insert into WorkStationEmployee(WorkStationID,employeeid,WorkStationEmployeeID) values('{0}','{1}',SYS_GUID()) ", dr("workStationid"), employeeid))
        Next
        Return DatabaseHelper.ExecuteSqlListTran(sqlList)
    End Function
End Class
